package com.redpack.redpost.util.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.redpack.redpost.connection.RedpostConexion;
import com.redpack.redpost.util.to.*;

public class MultipiezaArriboDao {

	
	public static int validaNumPaq( int cve_arribo ) throws SQLException{
		int total = 0;
		Connection conn = null;
		String sql = "" ;
		PreparedStatement ps = null ;
		ResultSet rs = null ;
		
		try
		{	
			conn = RedpostConexion.openConnection( ) ;
			conn.setAutoCommit( false ) ;		

			sql += "select count(*) as total from redpost.tbl_multipieza_arribo where activo=1 and cve_arribo="+cve_arribo;
			System.out.print(sql);
			ps = conn.prepareStatement( sql ) ;
			rs = ps.executeQuery();
			while (rs.next()) {
				total = rs.getInt("total");
			}
				
		}catch ( Exception e ) {
				e.printStackTrace( ) ;			
				return total ;
		} finally {
			if (conn != null) {
				conn.close();
	        }
		}
		
		return total;
	}
	
	public static boolean savePaqueteArribo(MultipiezaArriboTo _MultipiezaArriboTo) throws Exception {
		
		boolean res = false ;
		Connection conn = null;
		try
		{	
			conn = RedpostConexion.openConnection( ) ;
			conn.setAutoCommit( false ) ;
			PreparedStatement ps = null ;
			
			String sql = "" ;

			sql += "INSERT INTO redpost.tbl_multipieza_arribo(";
			sql += "cve_arribo, largo_dim_paq_cm, ancho_dim_paq_cm, alto_dim_paq_cm, ";
			sql += "largo_dim_paq_pulg, ancho_dim_paq_pulg, alto_dim_paq_pulg, peso_real_kg, ";
			sql += "peso_real_lb, peso_volumetrico_kg, peso_volumetrico_lb)";
			sql += "VALUES (";
			sql +=  _MultipiezaArriboTo.getCve_arribo() + ",";
			sql +=  _MultipiezaArriboTo.getLargo_dim_paq_cm() + ",";
			sql +=  _MultipiezaArriboTo.getAncho_dim_paq_cm() + ",";
			sql +=  _MultipiezaArriboTo.getAlto_dim_paq_cm() + ",";
			sql +=  _MultipiezaArriboTo.getLargo_dim_paq_pulg() + ",";
			sql +=  _MultipiezaArriboTo.getAncho_dim_paq_pulg() + ",";
			sql +=  _MultipiezaArriboTo.getAlto_dim_paq_pulg() + ",";
			sql +=  _MultipiezaArriboTo.getPeso_real_kg() + ",";
			sql +=  _MultipiezaArriboTo.getPeso_real_lb() + ",";
			sql +=  _MultipiezaArriboTo.getPeso_volumetrico_kg() + ",";
			sql +=  _MultipiezaArriboTo.getPeso_volumetrico_lb() + "";
			sql += ");";
		    		


				ps = conn.prepareStatement( sql ) ;
				ps.execute( ) ;

				res = true ;
				
				conn.commit( ) ;
				ps.close( ) ;
				conn.close( ) ;
				
				return res ;
				
			}
			catch ( Exception e ) 
			{
				e.printStackTrace( ) ;			
				return res ;
			} 
			finally 
			{
	            if (conn != null) 
	            {
	                conn.close();
	            }
			}
			
	}


	public static List getPaqueteArribo(int cve_arribo) throws Exception {
		
		Connection conn = RedpostConexion.openConnection( ) ;
		conn.setAutoCommit( false ) ;
		PreparedStatement ps = null ;
		ResultSet rs = null ;
		List paqueteArribo = new ArrayList(); 
		String sql = "" ;
		
		try{			
			sql = "select * from redpost.tbl_multipieza_arribo where activo = 1 and  cve_arribo ="+cve_arribo;
			ps = conn.prepareStatement( sql ) ;
			rs = ps.executeQuery();

            
			while (rs.next()) {
				MultipiezaArriboTo  row = new MultipiezaArriboTo();
				
				row.setCve_pieza(rs.getInt("cve_pieza"));
				row.setCve_arribo(rs.getInt("cve_arribo"));
				row.setLargo_dim_paq_cm(rs.getFloat("largo_dim_paq_cm"));
				row.setAncho_dim_paq_cm(rs.getFloat("ancho_dim_paq_cm"));
				row.setAlto_dim_paq_cm(rs.getFloat("alto_dim_paq_cm"));
				row.setLargo_dim_paq_pulg(rs.getFloat("largo_dim_paq_pulg"));
				row.setAncho_dim_paq_pulg(rs.getFloat("ancho_dim_paq_pulg"));
				row.setAlto_dim_paq_pulg(rs.getFloat("alto_dim_paq_pulg"));
				row.setPeso_real_kg(rs.getFloat("peso_real_kg"));
				row.setPeso_real_lb(rs.getFloat("peso_real_lb"));
				row.setPeso_volumetrico_kg(rs.getFloat("peso_volumetrico_kg"));
				row.setPeso_volumetrico_lb(rs.getFloat("peso_volumetrico_lb"));
				
		
				paqueteArribo.add( row );
			}
			
			conn.commit( ) ;
			ps.close( ) ;
			conn.close( ) ;
			
			return paqueteArribo ;
			
		}catch ( Exception e ) {
			e.printStackTrace( ) ;
			return paqueteArribo ;
		} finally {
	        if (conn != null) {
	            conn.close();
	        }
			
		}
	}	

	public static boolean delPaqueteArribo( int cve_pieza,int cve_arribo ) throws Exception {
		Connection conn = RedpostConexion.openConnection( ) ;
		conn.setAutoCommit( false ) ;
		PreparedStatement ps = null ;
		boolean res = false ;
		String sql = ""  ;
		
		try{

			sql = "update redpost.tbl_multipieza_arribo set activo=0 where cve_arribo=" +cve_arribo + " and cve_pieza="+cve_pieza;			
			ps = conn.prepareStatement( sql ) ;
			ps.execute();
			res = true ;
			
			conn.commit( ) ;
			ps.close( ) ;
			conn.close( ) ;
			return res ;
			
		}catch ( Exception e ) {
			e.printStackTrace( ) ;			
			return res ;
		} finally {
	        if (conn != null) {
	            conn.close();
	        }
			
		}
	}
	
}
